﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;
using System.Data.SqlClient;

namespace IHISCETeim.DAL
{
    public  class FXFinanceLeaseDAL
    {
        public static int Add(FXFinanceLeaseInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();


           // DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_Add",);

            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_Add");
            DB.AddInParameter(dbCommand, "AliasName", DbType.String, obj.AliasName);
            DB.AddInParameter(dbCommand, "CustomerName", DbType.String, obj.CustomerName);
            DB.AddInParameter(dbCommand, "MachineType", DbType.String, obj.MachineType);
            DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, obj.SerialNumber);
            DB.AddInParameter(dbCommand, "GPSNumber", DbType.String, obj.GPSNumber);
            DB.AddInParameter(dbCommand, "CustomerMobile", DbType.String, obj.CustomerMobile);
            DB.AddInParameter(dbCommand, "CustomerAddress", DbType.String, obj.CustomerAddress);

            SqlParameter LOrderDate = new SqlParameter("@LOrderDate", SqlDbType.DateTime);
            LOrderDate.IsNullable = true;
            if (obj.LOrderDate == DateTime.MinValue)
                LOrderDate.Value = DBNull.Value;
            else
                LOrderDate.Value = obj.LOrderDate;
            dbCommand.Parameters.Add(LOrderDate);

            DB.AddInParameter(dbCommand, "LeaseCompany", DbType.String, obj.LeaseCompany);
            DB.AddInParameter(dbCommand, "LTotalPrice", DbType.Decimal, obj.LTotalPrice);
            DB.AddInParameter(dbCommand, "LowProportion", DbType.Decimal, obj.LowProportion);
            DB.AddInParameter(dbCommand, "LExpiresYear", DbType.Decimal, obj.LExpiresYear);
            DB.AddInParameter(dbCommand, "LExpiresMonth", DbType.Int16, obj.LExpiresMonth);
            DB.AddInParameter(dbCommand, "LExpiresMonth2", DbType.Int16, obj.LExpiresMonth2);
            DB.AddInParameter(dbCommand, "LMonthRent", DbType.Decimal, obj.LMonthRent);
            DB.AddInParameter(dbCommand, "LMonthRent2", DbType.Decimal, obj.LMonthRent2);
            DB.AddInParameter(dbCommand, "LContractAmount", DbType.Decimal, obj.LContractAmount);
            DB.AddInParameter(dbCommand, "LByStages", DbType.Decimal, obj.LByStages);
            DB.AddInParameter(dbCommand, "LPay", DbType.Decimal, obj.LPay);
            DB.AddInParameter(dbCommand, "LPayStatus", DbType.Int16, obj.LPayStatus);
            DB.AddInParameter(dbCommand, "LMFee", DbType.Decimal, obj.LMFee);
            DB.AddInParameter(dbCommand, "LMFeeStatus", DbType.Int16, obj.LMFeeStatus);

            SqlParameter OGetDate = new SqlParameter("@OGetDate", SqlDbType.DateTime);
            OGetDate.IsNullable = true;
            if (obj.OGetDate == DateTime.MinValue)
                OGetDate.Value = DBNull.Value;
            else
                OGetDate.Value = obj.OGetDate;
            dbCommand.Parameters.Add(OGetDate);

            SqlParameter OSubmitDate = new SqlParameter("@OSubmitDate", SqlDbType.DateTime);
            OSubmitDate.IsNullable = true;
            if (obj.OSubmitDate == DateTime.MinValue)
                OSubmitDate.Value = DBNull.Value;
            else
                OSubmitDate.Value = obj.OSubmitDate;
            dbCommand.Parameters.Add(OSubmitDate);


            DB.AddInParameter(dbCommand, "OContractPost", DbType.String, obj.OContractPost);
            DB.AddInParameter(dbCommand, "OInsureStatus", DbType.Int16, obj.OInsureStatus);
            DB.AddInParameter(dbCommand, "ORemark", DbType.String, obj.ORemark);
            DB.AddInParameter(dbCommand, "UserId", DbType.Int32, obj.UserId);
            DB.AddInParameter(dbCommand, "RealName", DbType.String, obj.RealName);
            DB.AddInParameter(dbCommand, "LeaseStatus", DbType.Int16, obj.LeaseStatus);
#region 
            //SqlParameter PassDate = new SqlParameter("@PassDate", SqlDbType.DateTime);
            //PassDate.IsNullable = true;
            //if (obj.PassDate == DateTime.MinValue)
            //    PassDate.Value = DBNull.Value;
            //else
            //    PassDate.Value = obj.PassDate;
            //dbCommand.Parameters.Add(PassDate);

            //DB.AddInParameter(dbCommand, "ContractNum", DbType.String, obj.ContractNum);

            //SqlParameter LoansDate = new SqlParameter("@LoansDate", SqlDbType.DateTime);
            //LoansDate.IsNullable = true;
            //if (obj.LoansDate == DateTime.MinValue)
            //    LoansDate.Value = DBNull.Value;
            //else
            //    LoansDate.Value = obj.LoansDate;
            //dbCommand.Parameters.Add(LoansDate);

            //DB.AddInParameter(dbCommand, "MaterialStatus", DbType.Int16, obj.MaterialStatus);
            //DB.AddInParameter(dbCommand, "LEvadeRent", DbType.Decimal, obj.LEvadeRent);
            //DB.AddInParameter(dbCommand, "LPaidRent", DbType.Decimal, obj.LPaidRent);
            //DB.AddInParameter(dbCommand, "LResiduerRent", DbType.Decimal, obj.LResiduerRent);
            //DB.AddInParameter(dbCommand, "LTotalPeriods", DbType.Int16, obj.LTotalPeriods);
            //DB.AddInParameter(dbCommand, "LEvadePeriods", DbType.Int16, obj.LEvadePeriods);
            //DB.AddInParameter(dbCommand, "LPaidPeriods", DbType.Int16, obj.LPaidPeriods);
            //DB.AddInParameter(dbCommand, "LExpiryPeriods", DbType.Int16, obj.LExpiryPeriods);

            //SqlParameter LastPayDate = new SqlParameter("@LastPayDate", SqlDbType.DateTime);
            //LastPayDate.IsNullable = true;
            //if (obj.LastPayDate == DateTime.MinValue)
            //    LastPayDate.Value = DBNull.Value;
            //else
            //    LastPayDate.Value = obj.LastPayDate;
            //dbCommand.Parameters.Add(LastPayDate);

            //DB.AddInParameter(dbCommand, "GPSStatus", DbType.Int16, obj.GPSStatus);
            //DB.AddInParameter(dbCommand, "CleanUpStatus", DbType.Int16, obj.CleanUpStatus);
            //DB.AddInParameter(dbCommand, "LeaseStatus", DbType.Int16, obj.LeaseStatus);
#endregion
            return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
        }
        public static bool Delete(int FnncLsId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_Delete", FnncLsId);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }       
        public static IDataReader GetById(int FnncLsId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_GetById", FnncLsId);
            return DB.ExecuteReader(dbCommand);
        }
        public static DataTableCollection GetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }

        public static DataTableCollection FXFinanceLeaseWSBGetPagination(string WhereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLeaseWSB_GetPagination");
            DB.AddInParameter(dbCommand, "WhereSql", DbType.String, WhereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTableCollection dt = DB.ExecuteDataSet(dbCommand).Tables;

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }
        /// <summary>
        /// 导入程 序根据合同号，累计已付租,应付期数,已付期数
        /// </summary>
        /// <param name="ContractNum"></param>
        /// <param name="LPaidRent"></param>
        /// <param name="LEvadePeriods"></param>
        /// <param name="LPaidPeriods"></param>
        public static string ImportFromExcel(string ContractNum, decimal LPaidRent, short LEvadePeriods, decimal LPaidPeriods,string SerNum, string lastRealName)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ImportFromExcel", ContractNum, LPaidRent, LEvadePeriods, LPaidPeriods,SerNum, lastRealName);
            string x = "";
            try
            {
                x=  DB.ExecuteScalar(dbCommand).ToString();
              
            }
            catch (Exception exc)
            {
                x = exc.Message;
            }
            return x;
        }

        public static bool Update(FXFinanceLeaseInfo obj)
        {
            //return true;
            Database DB = DatabaseFactory.CreateDatabase();
            
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_Update");
            DB.AddInParameter(dbCommand, "FnncLsId", DbType.Int32, obj.FnncLsId);
            DB.AddInParameter(dbCommand, "AliasName", DbType.String, obj.AliasName);
            DB.AddInParameter(dbCommand, "CustomerName", DbType.String, obj.CustomerName);
            DB.AddInParameter(dbCommand, "MachineType", DbType.String, obj.MachineType);
            DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, obj.SerialNumber);
            DB.AddInParameter(dbCommand, "GPSNumber", DbType.String, obj.GPSNumber);
            DB.AddInParameter(dbCommand, "CustomerMobile", DbType.String, obj.CustomerMobile);
            DB.AddInParameter(dbCommand, "CustomerAddress", DbType.String, obj.CustomerAddress);

            SqlParameter LOrderDate = new SqlParameter("@LOrderDate", SqlDbType.DateTime);
            LOrderDate.IsNullable = true;
            if (obj.LOrderDate == DateTime.MinValue)
                LOrderDate.Value = DBNull.Value;
            else
                LOrderDate.Value = obj.LOrderDate;
            dbCommand.Parameters.Add(LOrderDate);

            DB.AddInParameter(dbCommand, "LeaseCompany", DbType.String, obj.LeaseCompany);
            DB.AddInParameter(dbCommand, "LTotalPrice", DbType.Decimal, obj.LTotalPrice);
            DB.AddInParameter(dbCommand, "LowProportion", DbType.Decimal, obj.LowProportion);
            DB.AddInParameter(dbCommand, "LExpiresYear", DbType.Decimal, obj.LExpiresYear);
            DB.AddInParameter(dbCommand, "LExpiresMonth", DbType.Int16, obj.LExpiresMonth);
            DB.AddInParameter(dbCommand, "LExpiresMonth2", DbType.Int16, obj.LExpiresMonth2);
            DB.AddInParameter(dbCommand, "LMonthRent", DbType.Decimal, obj.LMonthRent);
            DB.AddInParameter(dbCommand, "LMonthRent2", DbType.Decimal, obj.LMonthRent2);
            DB.AddInParameter(dbCommand, "LContractAmount", DbType.Decimal, obj.LContractAmount);
            DB.AddInParameter(dbCommand, "LByStages", DbType.Decimal, obj.LByStages);
            DB.AddInParameter(dbCommand, "LPay", DbType.Decimal, obj.LPay);
            DB.AddInParameter(dbCommand, "LPayStatus", DbType.Int16, obj.LPayStatus);
            DB.AddInParameter(dbCommand, "LMFee", DbType.Decimal, obj.LMFee);
            DB.AddInParameter(dbCommand, "LMFeeStatus", DbType.Int16, obj.LMFeeStatus);

            SqlParameter OGetDate = new SqlParameter("@OGetDate", SqlDbType.DateTime);
            OGetDate.IsNullable = true;
            if (obj.OGetDate == DateTime.MinValue)
                OGetDate.Value = DBNull.Value;
            else
                OGetDate.Value = obj.OGetDate;
            dbCommand.Parameters.Add(OGetDate);

            SqlParameter OSubmitDate = new SqlParameter("@OSubmitDate", SqlDbType.DateTime);
            OSubmitDate.IsNullable = true;
            if (obj.OSubmitDate == DateTime.MinValue)
                OSubmitDate.Value = DBNull.Value;
            else
                OSubmitDate.Value = obj.OSubmitDate;
            dbCommand.Parameters.Add(OSubmitDate);


            DB.AddInParameter(dbCommand, "OContractPost", DbType.String, obj.OContractPost);
            DB.AddInParameter(dbCommand, "OInsureStatus", DbType.Int16, obj.OInsureStatus);
            DB.AddInParameter(dbCommand, "ORemark", DbType.String, obj.ORemark);

            SqlParameter PassDate = new SqlParameter("@PassDate", SqlDbType.DateTime);
            PassDate.IsNullable = true;
            if (obj.PassDate == DateTime.MinValue)
                PassDate.Value = DBNull.Value;
            else
                PassDate.Value = obj.PassDate;
            dbCommand.Parameters.Add(PassDate);

            DB.AddInParameter(dbCommand, "ContractNum", DbType.String, obj.ContractNum);

            SqlParameter LoansDate = new SqlParameter("@LoansDate", SqlDbType.DateTime);
            LoansDate.IsNullable = true;
            if (obj.LoansDate == DateTime.MinValue)
                LoansDate.Value = DBNull.Value;
            else
                LoansDate.Value = obj.LoansDate;
            dbCommand.Parameters.Add(LoansDate);

            DB.AddInParameter(dbCommand, "MaterialStatus", DbType.Int16, obj.MaterialStatus);
            DB.AddInParameter(dbCommand, "LEvadeRent", DbType.Decimal, obj.LEvadeRent);
            DB.AddInParameter(dbCommand, "LPaidRent", DbType.Decimal, obj.LPaidRent);
            DB.AddInParameter(dbCommand, "LResiduerRent", DbType.Decimal, obj.LResiduerRent);
            DB.AddInParameter(dbCommand, "LTotalPeriods", DbType.Int16, obj.LTotalPeriods);
            DB.AddInParameter(dbCommand, "LEvadePeriods", DbType.Int16, obj.LEvadePeriods);
            DB.AddInParameter(dbCommand, "LPaidPeriods", DbType.Int16, obj.LPaidPeriods);
            DB.AddInParameter(dbCommand, "LExpiryPeriods", DbType.Int16, obj.LExpiryPeriods);

            SqlParameter LastPayDate = new SqlParameter("@LastPayDate", SqlDbType.DateTime);
            LastPayDate.IsNullable = true;
            if (obj.LastPayDate == DateTime.MinValue)
                LastPayDate.Value = DBNull.Value;
            else
                LastPayDate.Value = obj.LastPayDate;
            dbCommand.Parameters.Add(LastPayDate);

            DB.AddInParameter(dbCommand, "GPSStatus", DbType.Int16, obj.GPSStatus);
            DB.AddInParameter(dbCommand, "CleanUpStatus", DbType.Int16, obj.CleanUpStatus);
            DB.AddInParameter(dbCommand, "LeaseStatus", DbType.Int16, obj.LeaseStatus);
            DB.AddInParameter(dbCommand, "LMFeeDate", DbType.String, obj.LMFeeDate);
            DB.AddInParameter(dbCommand, "LDownAmountDate", DbType.String, obj.LDownAmountDate);
            DB.AddInParameter(dbCommand, "LPayYiFu", DbType.Decimal, obj.LPayYiFu);
            DB.AddInParameter(dbCommand, "LMFeeYiFu", DbType.Decimal, obj.LMFeeYiFu);

            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
             
        }
        /// <summary>
        /// 获取各进度的数量
        /// </summary>
        /// <param name="alisename">如果是区域经理使用，则需传入其所属的经销商查询条件</param>
        /// <returns>
        ///     审核中 ,审核通过,待放款,已放款
        /// </returns>
        public static DataTable GetLeaseStatusCount(string alisename)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_GetLeaseStatusCount", alisename);

            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取最后更新日期
        /// </summary>
        /// <returns></returns>
        public static string GetLastRefresDate()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_GetLastRefresDate");

            string x= DB.ExecuteScalar(dbCommand).ToString();
            if (string.IsNullOrEmpty(x))
                return DateTime.Now.ToString("yyyy-MM-dd");
            else
                return Convert.ToDateTime(x).ToString("yyyy-MM-dd");
        }
        /// <summary>
        /// 未上报获取最后更新日期
        /// </summary>
        /// <returns></returns>
        public static string GetWSBLasRefresDate()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLeaseWSB_GetLastRefresDate");

            string x = DB.ExecuteScalar(dbCommand).ToString();
            if (string.IsNullOrEmpty(x))
                return DateTime.Now.ToString("yyyy-MM-dd");
            else
                return Convert.ToDateTime(x).ToString("yyyy-MM-dd");
        }

        public static void WSBUpdate(int id, string remark)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLeaseWSB_Update",id,remark);
            DB.ExecuteScalar(dbCommand);
        }
       /// <summary>
        /// 未上报数据导入
       /// </summary>
        /// <param name="AliasName">经销商</param>
        /// <param name="CustomerName">用户</param>
        /// <param name="SerialNumber">机号</param>
        /// <param name="SaleDate">销售日期</param>
        /// <param name="LContractAmount">合同价格</param>
       /// <returns></returns>
        public static string ImportFromExcelWeiShangBao(string AliasName, string CustomerName, string SerialNumber, string SaleDate, decimal LContractAmount)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLeaseWSB_ImportFromExcelWeiShangBao", AliasName, CustomerName, SerialNumber, SaleDate, LContractAmount);
            string x = "";
            try
            {
                 x = DB.ExecuteScalar(dbCommand).ToString();
                 return x;
            }
            catch (Exception exc)
            {
                x = exc.Message;
            }
            return x;
        }
        /// <summary>
        /// 未上报数据导入
        /// 1.先清楚所有以前的未上报
        /// </summary>
        /// <returns></returns>
        public static bool DeleteWeiShangBao()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLeaseWSB_Delete");
            try
            {
                 DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }


        #region 报表部份
        /// <summary>
        /// 融资租赁报单情况汇总
        /// </summary>
        /// <returns></returns>
        static public DataTableCollection ReportGetRZZRBD(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetRZZRBD", sqlwhere);

            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 1 经销商放款台数占比分析
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportGetJXSFKTS()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetJXSFKTS");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 2融资租赁机型占比分析
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportGetRZZRJXZB()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetRZZRJXZB");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 3融资租赁期限占比分析
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportGetRZZRQXZB()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetRZZRQXZB");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 4融资租赁同期台数对比分析
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportGetRZZRTQTB()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetRZZRTQTB");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 5融资租赁同期台数对比分析
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportGetRZZRTQRZTB()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetRZZRTQRZTB");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 6各年度报单台数（按接单日期确认）（按接单日期 并是从2011年开始）
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportGetGNDBDTS()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetGNDBDTS");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        ///　经销商逾期单数汇总表
        /// </summary>
        /// <param name="aliseNameWhere">经销商名称　格式为 '''营业一部'',''风险部'''</param>
        /// <returns></returns>
        static public DataTableCollection ReportGetJXSYQFZ(string aliseName,string LeaseCompany, string orderBY)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetJXSYQFZ", aliseName, LeaseCompany, orderBY);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 管理费收益分析
	    ///按经销商区分
        /// </summary>
        /// <returns></returns>
        static public DataTableCollection ReportIncome()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportIncome");
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 管理费收益分析
        /// 按年份
        /// </summary>
        /// <param name="AliasName"></param>
        /// <returns></returns>
        static public DataTable ReportIncomeByAliasName(string AliasName)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportIncomeByAliasName", AliasName);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 管理费收益分析之净收入年对比
	    ///经销商按年份
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportIncomeLMFeeIncome()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportIncomeLMFeeIncome");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 经销商融资分析报表
        /// </summary>
        /// <param name="AliasName">经销商名称,为空时表示获取所有</param>
        /// <param name="LeaseCompany">融资租赁公司名称</param>
        /// <returns>返回两个表.第一个表,表示:所有的客户信息. 第二个表,表示:6期内的新客户</returns>
        static public DataTableCollection ReportGetRZFXBP(string AliasName, string LeaseCompany)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetRZFXBP", AliasName, LeaseCompany);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 获取最近一年有发生业务的经销商.
        /// 为经销商融资分析报表提供相关
        /// </summary>
        /// <returns></returns>
        static public DataTable GetAliasNameLastYear(string LeaseCompany)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_GetAliasNameLastYear", LeaseCompany);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取逾期2期以上的经销商
        /// </summary>
        /// <param name="LeaseCompany"></param>
        /// <returns></returns>
        static public DataTable ReportGetAliasNameJXSYQ2CSLSBP(string LeaseCompany)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetAliasNameJXSYQ2CSLSBP", LeaseCompany);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 经销商逾期2期及以上催收落实
        /// </summary>
        /// <param name="AliasName"></param>
        /// <param name="LeaseCompany"></param>
        /// <returns></returns>
        static public DataTable ReportGetJXSYQ2CSLSBP(string AliasName, string LeaseCompany)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetJXSYQ2CSLSBP", AliasName, LeaseCompany);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 经销商逾期2期及以上催收落实 汇总
        /// </summary>
        /// <param name="AliasName"></param>
        /// <param name="LeaseCompany"></param>
        /// <returns></returns>
        static public DataTableCollection ReportGetJXSYQ2CSLSBPTotal()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetJXSYQ2CSLSBP_Total");
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 9.逾期2期以上客户明累
        /// </summary>
        /// <param name="AliasName"></param>
        /// <returns></returns>
        static public DataTableCollection ReportYQ2QKFMX(string AliasName)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportYQ2QKFMX", AliasName);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 9.逾期2期以上客户明累_导出报表用
        /// </summary>
        /// <returns></returns>
        static public DataTable ReportYQ2QKFMXTotal()
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportYQ2QKFMXTotal");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取管理费及首付款报表
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        static public DataTableCollection ReportGetLPayLFMee(string sqlwhere)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("FXFinanceLease_ReportGetLPayLFMee", sqlwhere);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        #endregion
    }
}
